/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package putime.model;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import putime.db.DBModel;
import putime.newspaperagent.supplier.Supplier;

/**
 *
 * @author Khai
 */
public class ModelStock extends DBModel {

    public Vector connViewStock(Date date) {
        Vector vSup = new Vector<>();

        openConnect();
        try {
            prst = conn.prepareStatement("select DISTINCT SupID from tbl_stock where DateInput = ?");
            prst.setDate(1, date);
            rs = prst.executeQuery();
            while (rs.next()) {
                prst = conn.prepareStatement("select * from tbl_suppliers where SupID = ?");
                prst.setInt(1, rs.getInt(1));
                ResultSet rs1 = prst.executeQuery();
                while (rs1.next()) {
                    Supplier sp = new Supplier(rs1.getInt(1), rs1.getString(2), rs1.getString(3), rs1.getString(4));
                    vSup.add(sp + "    " + sp.getSupName());
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(ModelStock.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        return vSup;

    }

    public Vector connViewTable(int chon, Date date) {
        Vector vVTable = new Vector();
        openConnect();
        try {
            prst = conn.prepareStatement("select d.StockID, Name , d.ProID, Rate, QtyInward, QtyReturn from tbl_stock s, tbl_stockdetail d,\n"
                    + "tbl_products p where  s.SupID = ? and s.StockID = d.StockID and d.ProID = p.ProID and DateInPut = ?");
            prst.setInt(1, chon);
            prst.setDate(2, date);
            rs = prst.executeQuery();
            while (rs.next()) {
                Vector vRow = new Vector();
                vRow.add(rs.getInt(1));
                vRow.add(rs.getString(2));
                vRow.add(rs.getString(3));
                vRow.add(rs.getInt(4));
                vRow.add(rs.getInt(5));
                vRow.add(rs.getInt(6));
                vVTable.add(vRow);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ModelStock.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }

        return vVTable;

    }

    public Vector initSupAdd() {
        Vector vSup = new Vector();
        openConnect();
        try {
            prst = conn.prepareStatement("select * from tbl_suppliers");
            rs = prst.executeQuery();
            while (rs.next()) {
                vSup.add(rs.getInt(1) + "        " + rs.getString(2));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ModelStock.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        return vSup;

    }

    public Vector initProAdd(int chon) {
        Vector vPro = new Vector();
        openConnect();
        try {
            prst = conn.prepareStatement("select * from tbl_products where SupID = ?");
            prst.setInt(1, chon);
            rs = prst.executeQuery();
            while (rs.next()) {
                vPro.add(rs.getString(1) + "       " + rs.getString(2));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ModelStock.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        return vPro;

    }

    public boolean addStock(Date date, int supID, String proID, float rate, int inward, int returned) {
        int top1 = 0;
        openConnect();
        try {
            prst = conn.prepareStatement("insert tbl_stock values (?,?)");
            prst.setInt(1, supID);
            prst.setDate(2, date);
            prst.executeUpdate();

            prst = conn.prepareStatement("select top 1 * from tbl_stock order by StockID desc");
            rs = prst.executeQuery();
            while (rs.next()) {
                top1 = rs.getInt(1);
            }

            prst = conn.prepareStatement("insert tbl_stockdetail values (?,?,?,?,?)");
            prst.setInt(1, top1);
            prst.setString(2, proID);
            prst.setFloat(3, rate);
            prst.setInt(4, inward);
            prst.setInt(5, returned);
            int tam = prst.executeUpdate();
            return true;


        } catch (SQLException ex) {
            Logger.getLogger(ModelStock.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            closeConnect();
        }



    }

    public boolean checkExist(int supID, String proID, Date date) {
        openConnect();
        int dem = 0;
        try {
            prst = conn.prepareStatement("select * from tbl_stock s, tbl_stockdetail d where SupID = ? and ProID = ? \n"
                    + "and s.StockID=d.StockID and DateInput = ?");
            prst.setInt(1, supID);
            prst.setString(2, proID);
            prst.setDate(3, date);
            rs = prst.executeQuery();
            while (rs.next()) {
                dem++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ModelStock.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        if (dem == 0) {
            return true;
        } else {
            return false;
        }

    }

    public boolean updateStock(Date date, int stockID, float rate, int inward, int returned) {
        openConnect();
        try {
            prst = conn.prepareStatement("update tbl_stock set DateInput = ? where StockID = ?");
            prst.setDate(1, date);
            prst.setInt(2, stockID);
            prst.executeUpdate();
            prst = conn.prepareStatement("update tbl_stockdetail set Rate =?,QtyInward = ?, QtyReturn = ? where StockID = ?");
            prst.setFloat(1, rate);
            prst.setInt(2, inward);
            prst.setInt(3, returned);
            prst.setInt(4, stockID);
            prst.executeUpdate();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            closeConnect();
        }
       

    }
    
    public boolean deleteStock(int stockID){
        openConnect();
        try {
            prst = conn.prepareStatement("delete from tbl_stockdetail where StockID = ?");
            prst.setInt(1, stockID);
            prst.executeUpdate();
            prst = conn.prepareStatement("delete from tbl_stock where StockID = ?");
            prst.setInt(1, stockID);
            prst.executeUpdate();
            return true;            
        } catch (SQLException ex) {
            return false;
        }finally{
            closeConnect();
        }        
    }
}
